package com.info.zhiduoduo.repository.mapper.crowdsource;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.crowdsource.*;
import com.info.zhiduoduo.repository.entity.crowdsource.CrowdsourceTradeRecordItem;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * 智众包项目批量代付单条记录 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-09-25
 */
public interface CrowdsourceTradeRecordItemMapper extends BaseMapper<CrowdsourceTradeRecordItem> {

	@Select(" select cpi.user_type,cspi.*,ctr.pay_id from zdd_crowdsource_trade_record_item cspi " +
			" LEFT JOIN zdd_crowdsource_project_item cpi on cpi.id = cspi.project_item_id " +
			" LEFT JOIN zdd_crowdsource_trade_record ctr on ctr.batch_id = cspi.batch_id " +
			" where cspi.projectid = #{projectid} " +
			" and IF(ISNULL(#{status}), 1 = 1, cspi.status = #{status}) " +
			" and cspi.deleted = 0 " +
			" order by cspi.id desc ")
	List<CrowdsourceTradeRecordItemDto> getList(int projectid, Integer status);

	@Select(" select id from zdd_crowdsource_trade_record_item where repay = 0 and pay_type = 2 and status in(0,1) ")
	List<Integer> getRepayingItemIds();

	@Select(" select cspi.* from zdd_crowdsource_trade_record_item cspi " +
			" where cspi.batch_id = #{batchId} " +
			" and cspi.status in(0,1) " +
			" and cspi.repay = 0 ")
	List<CrowdsourceTradeRecordItem> getPayingItemList(int batchId);

	@Select(" select cspi.* from zdd_crowdsource_trade_record_item cspi " +
			" where cspi.batch_id = #{batchId} " +
			" and cspi.status = 2 " +
			" and cspi.repay = 0 ")
	List<CrowdsourceTradeRecordItem> getPayedItemList(int batchId);

	@Select(" SELECT @i := @i + 1 AS number, ctri.acctname AS name, ctri.acctno AS acctNo, " +
			" ctri.tradeamount AS tradeAmount, cp.project_name AS remarks, " +
			" IF( cpi.user_type = 1, ca.idno, ma.idno ) AS idNo, " +
			" IF( cpi.user_type = 1, ca.bankname, mb.bankname ) AS bankName, " +
			" IF( cpi.user_type = 1, ca.open_acct_bank_name, mb.open_acct_bank_name ) AS openAcctBankName " +
			" FROM zdd_crowdsource_trade_record_item ctri " +
			" LEFT JOIN zdd_crowdsource_project cp ON cp.projectid = ctri.projectid " +
			" LEFT JOIN zdd_crowdsource_project_item cpi ON cpi.id = ctri.project_item_id " +
			" LEFT JOIN zdd_crowdsource_account ca ON ca.crowdsourceid = cpi.userid AND cpi.user_type = 1 " +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = cpi.userid AND cpi.user_type = 2 " +
			" LEFT JOIN zdd_maker_bankcard mb ON mb.userid = cpi.userid AND mb.state = 1 AND cpi.user_type = 2, " +
			" ( SELECT @i := 0 ) r WHERE ctri.projectid = #{projectId} ")
	List<OfflinePayProjectExportDto> exportOfflinePayProjectById(int projectId);


	@Select(" SELECT ctri.*,cpi.pay_voucher FROM zdd_crowdsource_trade_record_item ctri " +
			" LEFT JOIN zdd_crowdsource_project_item cpi ON cpi.id = ctri.project_item_id " +
			" where ctri.projectid = #{projectId}")
	List<DetailedPersonnelListDto> queryDetailedPersonnelById(int projectId);

	@Select(" SELECT stu.username,if(isnull(ma.acctid),if(isnull(csa.acctid),'','自然人'),'创客') as usertype, " +
			" cp.party_id,c.compname,bm.business_manager, cp.createtime, ctri.finishtime, cp.project_name, ctri.acctname, " +
			" ctri.acctno, ctri.bankname, ctri.tradeamount, cpi.project_amount, cpi.integrated_tax_fee, cpi.project_service_charge,ctri.status, " +
			" IF(cpi.user_type=1,cs.phone,m.phone) AS phone, " +
			" IF(cpi.user_type=1,csa.idno,ma.idno) AS idno,bmm.business_manager as merchandiserName,cp.pay_id,pay.pay_name_alias, " +
			" cp.work_type,cp.supplement,ccp.company_name AS partyName,cp.invoice_content,zts.tax_source_name " +
			" FROM zdd_crowdsource_trade_record_item ctri " +
			" left join zdd_crowdsource_project cp on cp.projectid = ctri.projectid " +
			" left join zdd_crowdsource_project_item cpi on cpi.id = ctri.project_item_id " +
			" left join zdd_company c on c.compid = cp.compid " +
			" LEFT JOIN zdd_company_account ca on ca.compid = cp.compid " +
			" LEFT JOIN zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
			" LEFT JOIN zdd_maker m ON m.userid = cpi.userid AND cpi.user_type = 2 " +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = cpi.userid AND cpi.user_type = 2 " +
			" left join zdd_maker_studio  stu on stu.userid=ma.userid " +
			" LEFT JOIN zdd_crowdsource cs ON cs.crowdsourceid = cpi.userid AND cpi.user_type = 1 " +
			" LEFT JOIN zdd_crowdsource_account csa ON csa.crowdsourceid = cpi.userid AND cpi.user_type = 1 " +
			" left join zdd_business_manager bmm on bmm.business_manager_id = ca.merchandiser_id  " +
			" left join zdd_crowdsource_company_party ccp on ccp.id = cp.party_id " +
			" left join zdd_payment pay on pay.id = cp.pay_id " +
			" left join zdd_tax_source zts on zts.tax_source_id = cp.tax_source_id " +
			" where " +
			" cp.state = 4 " +
			" AND IF(#{workType}=0, 1=1, cp.work_type = #{workType}) " +
			" AND ctri.status in(1,2) " +
			" AND c.compname like '%${name}%' " +
			" AND cp.deleted = 0 " +
			" AND cp.project_name like '%${projectName}%' " +
			" AND IF(#{compid} = 0 ,1 = 1 , cp.compid = #{compid}) " +
			" AND ctri.createtime BETWEEN #{startDate} and #{endDate}")
	List<ExportProjectTransactionDetailDto> exportProjectTransactionDetail(String name, Date startDate,Date endDate,String projectName,int compid, Integer workType);

	@Select(" SELECT stu.username,if(isnull(ma.acctid),if(isnull(csa.acctid),'','自然人'),'创客') as usertype, " +
			" cp.party_id,c.compname,bm.business_manager, cp.createtime, ctri.finishtime, cp.project_name, ctri.acctname, " +
			" ctri.acctno, ctri.bankname, ctri.tradeamount, cpi.project_amount, cpi.integrated_tax_fee, cpi.project_service_charge,ctri.status, " +
			" IF(cpi.user_type=1,cs.phone,m.phone) AS phone, " +
			" IF(cpi.user_type=1,csa.idno,ma.idno) AS idno,bmm.business_manager as merchandiserName,cp.pay_id, " +
			" cp.work_type,cp.supplement,ccp.company_name AS partyName  " +
			" FROM zdd_crowdsource_trade_record_item ctri " +
			" left join zdd_crowdsource_project cp on cp.projectid = ctri.projectid " +
			" left join zdd_crowdsource_project_item cpi on cpi.id = ctri.project_item_id " +
			" left join zdd_company c on c.compid = cp.compid " +
			" LEFT JOIN zdd_company_account ca on ca.compid = cp.compid " +
			" LEFT JOIN zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
			" LEFT JOIN zdd_maker m ON m.userid = cpi.userid AND cpi.user_type = 2 " +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = cpi.userid AND cpi.user_type = 2 " +
			" left join zdd_maker_studio  stu on stu.userid=ma.userid " +
			" LEFT JOIN zdd_crowdsource cs ON cs.crowdsourceid = cpi.userid AND cpi.user_type = 1 " +
			" LEFT JOIN zdd_crowdsource_account csa ON csa.crowdsourceid = cpi.userid AND cpi.user_type = 1 " +
			" left join zdd_business_manager bmm on bmm.business_manager_id = ca.merchandiser_id  " +
			" left join zdd_crowdsource_company_party ccp on ccp.id = cp.party_id " +
			" where " +
			" cp.state = 4 and ctri.deleted = 0 " +
			" AND IF(#{workType}=0, 1=1, cp.work_type = #{workType}) " +
			" AND ctri.status in(1,2) " +
			" AND c.compname like '%${name}%' " +
			" AND cp.deleted = 0 " +
			" AND cp.project_name like '%${projectName}%' " +
			" AND IF(#{compid} = 0 ,1 = 1 , cp.compid = #{compid}) " +
			" AND cp.createtime BETWEEN #{startDate} and #{endDate}")
	List<ExportProjectTransactionDetailCompanyDto> exportProjectTransactionDetailCompany(String name, Date startDate,Date endDate,String projectName,int compid, Integer workType);

	@Select(" SELECT stu.username,if(isnull(ma.acctid),if(isnull(csa.acctid),'','自然人'),'创客') as usertype, " +
			" cp.party_id,c.compname,bm.business_manager, cp.createtime, ctri.finishtime, cp.project_name, ctri.acctname, " +
			" ctri.acctno, ctri.bankname, ctri.tradeamount, cpi.project_amount, cpi.integrated_tax_fee, cpi.project_service_charge,ctri.status, " +
			" IF(cpi.user_type=1,cs.phone,m.phone) AS phone, " +
			" IF(cpi.user_type=1,csa.idno,ma.idno) AS idno,bmm.business_manager as merchandiserName,cp.pay_id," +
			" cp.work_type,cp.supplement,ccp.company_name AS partyName   " +
			" FROM zdd_crowdsource_trade_record_item ctri " +
			" left join zdd_crowdsource_project cp on cp.projectid = ctri.projectid " +
			" left join zdd_crowdsource_project_item cpi on cpi.id = ctri.project_item_id " +
			" left join zdd_company c on c.compid = cp.compid " +
			" LEFT JOIN zdd_company_account ca on ca.compid = cp.compid " +
			" LEFT JOIN zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
			" LEFT JOIN zdd_maker m ON m.userid = cpi.userid AND cpi.user_type = 2 " +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = cpi.userid AND cpi.user_type = 2 " +
			" left join zdd_maker_studio  stu on stu.userid=ma.userid " +
			" LEFT JOIN zdd_crowdsource cs ON cs.crowdsourceid = cpi.userid AND cpi.user_type = 1 " +
			" LEFT JOIN zdd_crowdsource_account csa ON csa.crowdsourceid = cpi.userid AND cpi.user_type = 1 " +
			" left join zdd_business_manager bmm on bmm.business_manager_id = ca.merchandiser_id  " +
			" left join zdd_crowdsource_company_party ccp on ccp.id = cp.party_id " +
			" where " +
			" cp.state = 4 " +
			" AND IF(#{workType}=0, 1=1, cp.work_type = #{workType}) " +
			" AND ctri.status in(1,2) " +
			" AND c.compname like '%${name}%' " +
			" AND cp.deleted = 0 " +
			" AND cp.project_name like '%${projectName}%' " +
			" AND IF(#{compid} = 0 ,1 = 1 , cp.compid = #{compid}) " +
			" AND cp.createtime BETWEEN #{startDate} and #{endDate}")
	List<ExportProjectTransactionDetailsCompanyDto> exportProjectTransactionsDetailCompany(String name, Date startDate,Date endDate,String projectName,int compid,Integer workType);

	@Select("SELECT ccp.company_name AS partyName, " +
			"ctri.tradeamount, " +
			"cp.tax_source_cost_rate, " +
			"cpi.createtime, " +
			"ctri.tradetime, " +
			"ctri.`status`, " +
			"cp.project_name,cp.tax_source_id, " +
			"ctri.acctno, " +
			"ctri.acctname, " +
			"ctri.bankname, " +
			"cpi.user_type, " +
			"cpi.userid," +
			"ctri.batch_no," +
			"ctri.updatetime," +
			"zts.tax_source_name,zts.affiliated_park,zts.department,zts.salesman " +
			"FROM zdd_crowdsource_trade_record_item ctri " +
			"LEFT JOIN zdd_crowdsource_project_item cpi ON  cpi.id  = ctri.project_item_id " +
			"LEFT JOIN zdd_crowdsource_project cp ON cpi.projectid = cp.projectid " +
			"LEFT JOIN zdd_crowdsource_company_party ccp ON ccp.id = cp.party_id " +
			"LEFT JOIN zdd_tax_source zts ON zts.tax_source_id = cp.tax_source_id " +
			"WHERE cp.state IN(2,3,4) " +
			"AND ctri.deleted = 0 " +
			"AND ctri.repay =0 " +
			"AND ctri.tradetime between #{startDate} and #{endDate}  " +
			"AND IF(ISNULL(#{nameOfPayee}) OR #{nameOfPayee}='',1=1, ctri.acctname LIKE CONCAT('%',#{nameOfPayee},'%') ) " +
			"AND IF(ISNULL(#{merchantName}) OR #{merchantName}='',1=1, ccp.company_name LIKE CONCAT('%',#{merchantName},'%') ) " +
			"AND if(ISNULL(#{taxSourceId}) or #{taxSourceId} = '', 1=1, cp.tax_source_id IN(0${taxSourceId}) ) " +
			"AND if(ISNULL(#{transactionStatus}),1=1,ctri.status = #{transactionStatus})  " +
			"AND if(#{itemType} = 0,1=1,cp.work_type = #{itemType})  " +
			"AND if(ISNULL(#{whetherToSupplement}),1=1,cp.supplement = #{whetherToSupplement})  " +
			"ORDER BY ctri.createtime DESC ")
	List<PersonalCapitalFlowDto> personalCapitalFlow(@Param("nameOfPayee")String nameOfPayee,
                                                     @Param("transactionStatus")Integer transactionStatus,
                                                     @Param("merchantName")String merchantName,
                                                     @Param("itemType")Integer itemType,
                                                     @Param("whetherToSupplement")Integer whetherToSupplement,
                                                     @Param("taxSourceId")String taxSourceId,
                                                     @Param("startDate")Date startDate,
                                                     @Param("endDate")Date endDate);
}
